package com.zhouxiaoxuan13.promotionservice.repository;

import com.zhouxiaoxuan13.promotionservice.entity.Promotion;
import com.zhouxiaoxuan13.promotionservice.dto.PromotionDetailDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface PromotionRepository extends JpaRepository<Promotion, Long> {

    // Find active promotions (active flag is true AND current date is within start/end dates)
    List<Promotion> findByActiveTrueAndStartDateBeforeAndEndDateAfter(LocalDateTime startDate, LocalDateTime endDate);

    // Find promotions that are currently active (using the active flag and current time)
    default List<Promotion> findCurrentlyActivePromotions() {
        LocalDateTime now = LocalDateTime.now();
        return findByActiveTrueAndStartDateBeforeAndEndDateAfter(now, now);
    }

    // Find promotions by name containing a string (case-insensitive)
    List<Promotion> findByNameContainingIgnoreCase(String name);

    // Find promotions by fresh product ID
    List<Promotion> findByFreshProductId(Long freshProductId);

    // Find active promotions by fresh product ID
    @Query("SELECT p FROM Promotion p WHERE p.active = true AND p.freshProductId = :freshProductId")
    List<Promotion> findActivePromotionsByFreshProductId(Long freshProductId);

    // Get all promotions with product details using native query
    @Query(value = "SELECT " +
           "p.id, p.name, p.description, p.promotion_price, p.start_date, p.end_date, p.active, p.created_time, p.updated_time, " +
           "fp.id as fresh_product_id, fp.name as product_name, fp.description as product_description, " +
           "fp.category, fp.specifications, fp.original_price, fp.stock_quantity " +
           "FROM promotions p " +
           "JOIN fresh_products fp ON p.fresh_product_id = fp.id",
           nativeQuery = true)
    List<Object[]> findAllPromotionsWithProductDetailsNative();

    // Get active promotions with product details using native query
    @Query(value = "SELECT " +
           "p.id, p.name, p.description, p.promotion_price, p.start_date, p.end_date, p.active, p.created_time, p.updated_time, " +
           "fp.id as fresh_product_id, fp.name as product_name, fp.description as product_description, " +
           "fp.category, fp.specifications, fp.original_price, fp.stock_quantity " +
           "FROM promotions p " +
           "JOIN fresh_products fp ON p.fresh_product_id = fp.id " +
           "WHERE p.active = true AND p.start_date <= NOW() AND p.end_date >= NOW()",
           nativeQuery = true)
    List<Object[]> findActivePromotionsWithProductDetailsNative();

}
